package batch.import_user;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.log4j.*;

import java.util.ArrayList;
import java.sql.*;
import java.io.*;

import batch.common.db.ConnectionManager;

public class makeTempTableWithoutIdnPw
{
    //private final String FILE_NAME  = "/home01/sfc/batch/import_user/data/zaf_200710_i600_20071022.txt";
    private final String FILE_NAME  = "/home01/sfc/batch/import_user/data/zaf_200710_royaladventure_20071022.txt";
    private final String TABLE_NAME = "G_ZAF_IMPORT_TEMP";

    //arrStrData[0] : email
    //arrStrData[1] : firstname
    //arrStrData[2] : lastname
    //arrStrData[3] : hp_num
    //arrStrData[4] : hp_brand

    public static void main(String[] args)
    {
        Logger.getLogger("import_user.makeTempTableWithoutIdnPw").info("( * ) Start.");

        makeTempTableWithoutIdnPw obj = new makeTempTableWithoutIdnPw();
        obj.init();

        Logger.getLogger("import_user.makeTempTableWithoutIdnPw").info("( * ) End.");
    }


    public void init(){
        Connection conn = null;

        BufferedReader bfrdForFile = null;

        ArrayList params = new ArrayList();

        String strTemp = "";
        StringBuffer sQuery = new StringBuffer()
                             .append("INSERT INTO " + TABLE_NAME + "( SEQ    	\n")
                             .append("                              , EMAIL  	\n")
                             .append("                              , FNAME  	\n")
                             .append("                              , LNAME  	\n")
                             .append("                              , HP_NUM 	\n")
                             .append("                              , HP_BRAND 	\n")
                             .append("                              , OPTIN ) 	\n")
                             .append("                        VALUES( ?      	\n")
                             .append("                              , ?      	\n")
                             .append("                              , ?      	\n")
                             .append("                              , ?      	\n")
                             .append("                              , ?      	\n")
                             .append("                              , ?      	\n")
                             .append("                              , 'Y')     	\n");

        Logger.getLogger("import_user.makeTempTableWithoutIdnPw").info(FILE_NAME);

        try{
            bfrdForFile = new BufferedReader(new InputStreamReader(new FileInputStream(FILE_NAME), "ISO-8859-1"));

            conn = ConnectionManager.getConnection();
            conn.setAutoCommit(true);

            QueryRunner qRunner = new QueryRunner();

            for(int i=0; (strTemp = bfrdForFile.readLine()) != null; i++) {
                String arrStrData[] = StringUtils.splitPreserveAllTokens(strTemp, "\t");

                for(int l=0;l<arrStrData.length;l++){
                    arrStrData[l] = StringUtils.stripToEmpty(arrStrData[l]);
                }
                String tmpHp_num = "";
                if(arrStrData[3].length() > 0){
                	tmpHp_num = arrStrData[3].substring(2);
                }
                
                params.clear();
                params.add(new Long(i + 1)					);  //SEQ
                params.add(arrStrData[0]                    );  //EMAIL
                params.add(arrStrData[1]                    );  //FNAME
                params.add(arrStrData[2]                    );  //LNAME
                params.add(tmpHp_num						);  //HP_MUM
                params.add(this.getBrandCode(arrStrData[4]) );  //HP_BRAND

                try{
                    qRunner.update(conn, sQuery.toString(), params.toArray());
                }
                catch(SQLException ex){
                    ex.printStackTrace();
                    Logger.getLogger("import_user.makeTempTableWithoutIdnPw").info(ex.toString());
                }

                if(i % 500 == 0){
                    Logger.getLogger("import_user.makeTempTableWithoutIdnPw").info("here we are == " + i);
                }
            }
        }
        catch(Exception ex) {
            ex.printStackTrace();
            Logger.getLogger("import_user.makeTempTableWithoutIdnPw").info(ex.toString());
        }
        finally {
            if (bfrdForFile != null) try { bfrdForFile.close(); } catch (Exception se) {}
            if (conn != null) try { conn.close(); } catch (Exception se) {}
        }
    }
    
    private String getBrandCode(String strBrand){
        if(strBrand.toLowerCase().equals("no phone")){
            return "0103001";
        }
        else if(strBrand.toLowerCase().equals("nophone")){
            return "0103001";
        }
        else if(strBrand.toLowerCase().equals("samsung")){
            return "0103002";
        }
        else if(strBrand.toLowerCase().equals("nokia")){
            return "0103003";
        }
        else if(strBrand.toLowerCase().equals("motorola")){
            return "0103004";
        }
        else if(strBrand.toLowerCase().equals("siemens")){
            return "0103005";
        }
        else if(strBrand.toLowerCase().equals("sony ericsson")){
            return "0103006";
        }
        else if(strBrand.toLowerCase().equals("alcatel")){
            return "0103007";
        }
        else if(strBrand.toLowerCase().equals("sagem")){
            return "0103008";
        }
        else if(strBrand.toLowerCase().equals("philips")){
            return "0103009";
        }
        else if(strBrand.toLowerCase().equals("panasonic")){
            return "0103010";
        }
        else if(strBrand.toLowerCase().equals("lg")){
            return "0103011";
        }
        else if(strBrand.toLowerCase().equals("sharp")){
            return "0103012";
        }
        else if(strBrand.toLowerCase().equals("nec")){
            return "0103013";
        }
        else{
            return "0103999";
        }
    }
}
